
//
//  DBHelper.m
//  Say
//
//  Created by Duc Trinh on 6/6/12.
//  Copyright (c) 2012 DHBK. All rights reserved.
//

#import "DBHelper.h"
#import "sqlite3.h"
#import "Offer.h"
@interface DBHelper()


- (void) bindString:(sqlite3_stmt *)stmt atIndex:(int)paramIndex withText:(NSString *)text;
- (NSString *) readString:(sqlite3_stmt *)stmt atIndex:(int)columnIndex withDefaultValue:(NSString *)defaultValue;
@end

@implementation DBHelper
static DBHelper *instance = nil;

+ (DBHelper *)shared
{
    if (instance == nil) {
        instance = [[DBHelper alloc] init];
    }
    return instance;
}

- (id)init
{
    if (self = [super init]) {
        
    }
    return self;
}
- (void) deleteDB
{
    NSString *filePath = [self getDBPath];
    if([[NSFileManager defaultManager] fileExistsAtPath:filePath]){
        [[NSFileManager defaultManager] removeItemAtPath:filePath error:nil];
    }
}
- (int) openDB
{
    sqlite3 *database = nil;
    sqlite3_stmt *statement = nil;
    int result = sqlite3_open([[self getDBPath] UTF8String], &database);
    return result;
}
- (void) initDB
{
    
    sqlite3 *database = nil;
    sqlite3_stmt *statement = nil;
    int result = sqlite3_open([[self getDBPath] UTF8String], &database);    
    if(result != SQLITE_OK)
    {
        sqlite3_close(database);
        UIAlertView *view = [[UIAlertView alloc]
                             initWithTitle: @"Database Error"
                             message: @"Failed to open database."
                             delegate: self
                             cancelButtonTitle: @"OK" otherButtonTitles: nil];
        [view show];
        //[view autorelease];
        return;
    }
    else
    {
        
        /* Create Table Offers*/
        NSString *offersTable = @"CREATE TABLE IF NOT EXISTS OFFERS"
        "("
        "ID INTEGER 						PRIMARY KEY AUTOINCREMENT,"
        "PROMOTION                          VARCHAR(255)"
        ")";
        
        if(sqlite3_prepare_v2(database, [offersTable UTF8String], -1, &statement, NULL) != SQLITE_OK)
        {
            NSLog(@"DBHelper - Error preparing create statement for MESSAGE table: %s", sqlite3_errmsg(database));
        }
        else
        {
            if(sqlite3_step(statement) != SQLITE_DONE)
            {
                NSLog(@"DBHelper - Error create MESSAGE table: %s", sqlite3_errmsg(database));
            }
        }
        sqlite3_finalize(statement);        
        sqlite3_close(database);

    }         
}


- (NSString *) getDBPath {	
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
	NSString *documentsDir = [paths objectAtIndex:0];
    NSLog(@"data base local %@", [documentsDir stringByAppendingPathComponent:@"IMXDB.sqlite"]);
	return [documentsDir stringByAppendingPathComponent:@"IMXDB.sqlite"];
}


#pragma Offer
- (NSError *)saveOffers:(NSString *)offer
{
    NSString *tableName = @"OFFERS";
    NSError *error = nil;
    sqlite3 *database;
    sqlite3_stmt *statement;
    int result = sqlite3_open([[self getDBPath] UTF8String], &database);
    if(result != SQLITE_OK)
    {
        sqlite3_close(database);
        NSMutableDictionary *errorDetail = [NSMutableDictionary dictionary];
        [errorDetail setValue:@"DBHelper - Error opening Say database" forKey:NSLocalizedDescriptionKey];
        error = [NSError errorWithDomain:@"Demo" code:100 userInfo:errorDetail];
    }
    else
    {

            NSString *saveChatQuery = [NSString stringWithFormat:@"INSERT INTO %@ (PROMOTION) VALUES(?)", tableName];
        
            if(sqlite3_prepare_v2(database, [saveChatQuery UTF8String], -1, &statement, NULL) != SQLITE_OK)
            {
                NSString *errorString = [NSString stringWithFormat:@"DBHelper - Error preparing sqlite statement for insert into %@ table: %s", tableName, sqlite3_errmsg(database)];
                NSMutableDictionary *errorDetail = [NSMutableDictionary dictionary];
                [errorDetail setValue:errorString forKey:NSLocalizedDescriptionKey];
                error = [NSError errorWithDomain:@"DBHelper" code:200 userInfo:errorDetail];
            }
            else
            {
                      
                    [self bindString:statement atIndex:1 withText:offer];
                        
                    if(sqlite3_step(statement) != SQLITE_DONE)
                    {
                        NSString *errorString = [NSString stringWithFormat:@"DBHelper - Error doing insert query for %@ table: %s", tableName, sqlite3_errmsg(database)];
                        NSMutableDictionary *errorDetail = [NSMutableDictionary dictionary];
                        [errorDetail setValue:errorString forKey:NSLocalizedDescriptionKey];
                        error = [NSError errorWithDomain:@"DBHelper" code:300 userInfo:errorDetail];
                    }
                    sqlite3_reset(statement);
            
            }
            sqlite3_finalize(statement);
            sqlite3_close(database);
    }
    return error;

}
- (NSMutableArray *)getOffers
{
    sqlite3 *database;
    sqlite3_stmt *statement;
    
    NSMutableArray *resultList = [[NSMutableArray alloc] init];
    
    int result = sqlite3_open([[self getDBPath] UTF8String], &database);
    if(result != SQLITE_OK)
    {
        sqlite3_close(database);
        NSMutableDictionary *errorDetail = [NSMutableDictionary dictionary];
        [errorDetail setValue:@"DBHelper - Error opening Say database" forKey:NSLocalizedDescriptionKey];
        
    }
    else
    {
        NSString *getUserChatInfoQuery = @"SELECT * FROM OFFERS";
        if(sqlite3_prepare_v2(database, [getUserChatInfoQuery UTF8String], -1, &statement, NULL) != SQLITE_OK)
        {
            NSString *errorString = [NSString stringWithFormat:@"DBHelper - Error preparing sqlite statement for query unread user chat list: %s", sqlite3_errmsg(database)];
            NSMutableDictionary *errorDetail = [NSMutableDictionary dictionary];
            [errorDetail setValue:errorString forKey:NSLocalizedDescriptionKey];
            
        }
        else
        {
            char *promotionChars;
            while(sqlite3_step(statement) == SQLITE_ROW)
            {
                Offer *offer = [[Offer alloc] init];
                offer.index = sqlite3_column_int(statement, 0);
                promotionChars = (char *) sqlite3_column_text(statement, 1);
                offer.offer = [[NSString alloc] initWithUTF8String:promotionChars];
                        
                [resultList addObject:offer];

            }
            
        }
            
    }
    sqlite3_finalize(statement);
    sqlite3_close(database);
    
    NSMutableArray *returnResult = [NSMutableArray arrayWithArray:resultList];
    return returnResult;

}
#pragma Delete all rows
- (void)deleteOffer:(int)index
{
    sqlite3 *database;
    sqlite3_stmt *statement;
    
    NSMutableArray *resultList = [[NSMutableArray alloc] init];
    
    int result = sqlite3_open([[self getDBPath] UTF8String], &database);
    if(result != SQLITE_OK)
    {
        sqlite3_close(database);
        NSMutableDictionary *errorDetail = [NSMutableDictionary dictionary];
        [errorDetail setValue:@"DBHelper - Error opening Say database" forKey:NSLocalizedDescriptionKey];
        
    }
    else
    {
        [self deleteRowsFromTable:index database:database];
    }
    

}
- (NSError *)deleteRowsFromTable:(int)index database:(sqlite3 *)database
{
    NSError *error = nil;
    sqlite3_stmt *statement;
    NSString *deleteQuery = [NSString stringWithFormat:@"DELETE FROM %@ WHERE ID = %d", @"OFFERS",index];
    
    if(sqlite3_prepare_v2(database, [deleteQuery UTF8String], -1, &statement, NULL) != SQLITE_OK)
    {
        NSString *errorString = [NSString stringWithFormat:@"DBHelper - Error preparing sqlite statement for insert into %@ table: %s", @"OFFERS", sqlite3_errmsg(database)];
        NSMutableDictionary *errorDetail = [NSMutableDictionary dictionary];
        [errorDetail setValue:errorString forKey:NSLocalizedDescriptionKey];
        error = [NSError errorWithDomain:@"DBHelper" code:200 userInfo:errorDetail];
    }
    else
    {
        sqlite3_step(statement);
    }
    sqlite3_finalize(statement);
    return  error;
}

#pragma mark -
#pragma mark - Support methods
- (void) bindString:(sqlite3_stmt *)stmt atIndex:(int)paramIndex withText:(NSString *)text
{
    if (text == nil)
    {
        sqlite3_bind_null(stmt, paramIndex);
    }
    else {
        sqlite3_bind_text(stmt, paramIndex, [text UTF8String], -1, SQLITE_TRANSIENT);
    }
}


@end
